import datetime
import math
import os
import openpyxl
import xlwings as xw
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif']='SimHei'
import pandas as pd
import numpy as np
import random
from scipy.interpolate import interp1d
import warnings
warnings.filterwarnings('ignore')

def re_type(i='pwh'):  # 将时间格式转换为以秒作为单位(根据有无'.'分情况讨论)
    i=str(i).replace('0 days ','')
    start_idx=i.index(':')
    end_idx=i.rindex(':')
    if i.count('.')>0:  #该时间含有毫秒部分(形如02:05:30.100)
        end_idx2=i.index('.')
        hour=int(i[:start_idx])  # 提取小时数
        minute=int(i[start_idx+1:end_idx])  # 提取分钟数
        second=int(i[end_idx+1:end_idx2])  # 提取秒数
        millsecond=int(i[end_idx2+1:])
        t=hour*3600+minute*60+second+millsecond/1000  # 时刻以秒作为单位
        return t
    else:
        hour=int(i[:start_idx])  # 提取小时数
        minute=int(i[start_idx+1:end_idx])  # 提取分钟数
        second=int(i[end_idx+1:])  # 提取秒数
        t=hour*3600+minute*60+second  # 时刻以秒作为单位
        return t

file=r'C:\Users\数据处理\Desktop\六组1000圈\101_2_赣锋__20240720175203000_并联通道.xlsx'  #已交付的
sheet_cycle='循环层'

file2=r'C:\Users\数据处理\Desktop\性能循环\文件2.xlsx'
record=pd.read_excel(file2,sheet_name='记录层')
interval = 10  # 时间采样间隔(设定为10秒)
#假设记录层时间采样间隔是10s
df=pd.read_excel(file,sheet_name=sheet_cycle)
print(f'df的值为{df}')
temp_material_file=r'C:\Users\数据处理\Desktop\记录层素材\25_3_0ALCBPTTEP56ADE750000023_20240716291-71173204-011-ZWZ.xlsx'
step_record_material=r'C:\Users\数据处理\Desktop\完成\记录层、工步层素材(1).xlsx'
df_record=pd.read_excel(step_record_material,sheet_name='记录层')
temp_material=pd.read_excel(temp_material_file,sheet_name='记录层')
temp_material['循环序号']=temp_material['循环序号'].astype(int)
temp_material2=temp_material[['记录号','循环序号','工步状态','温度1(℃)']].copy()
temp_material2_charge=temp_material2[(temp_material2['循环序号']==42)&(temp_material2['工步状态'].str.contains('充电'))].copy()
temp_material2_discharge=temp_material2[(temp_material2['循环序号']==42)&(temp_material2['工步状态'].str.contains('放电'))].copy()

func_charge_temp=interp1d(x=temp_material2_charge['记录号'],y=temp_material2_charge['温度1(℃)'],kind='cubic')
func_discharge_temp=interp1d(x=temp_material2_discharge['记录号'],y=temp_material2_discharge['温度1(℃)'],kind='cubic')

stop_after_charge=df_record[(df_record['循环序号']==3)&(df_record['工步状态']=='静置')&(df_record['步次']==9)].copy()  #充电后静置部分
stop_after_discharge=df_record[(df_record['循环序号']==3)&(df_record['工步状态']=='静置')&(df_record['步次']==11)].copy()  #放电后静置部分

# 步次=7
five_hours_stop=df_record[df_record['步次']==7].copy()    #5小时静置

stop_after_charge['记录号']=[i for i in range(stop_after_charge.shape[0])]
stop_after_discharge['记录号']=[i for i in range(stop_after_discharge.shape[0])]

stop_func_c=interp1d(x=stop_after_charge['记录号'],y=stop_after_charge['实际电压(V)'],kind='cubic')
stop_func_d=interp1d(x=stop_after_discharge['记录号'],y=stop_after_discharge['实际电压(V)'],kind='cubic')

file3=r'C:\Users\数据处理\Desktop\中汽研模板(1).xlsx'
df_zqy=pd.read_excel(file3,sheet_name='记录层')
print(f'df_zqy的值为{df_zqy}')

#初始化部分的第一个循环充电部分
initial_charge_1=df_zqy[(df_zqy['循环序号']==1)&(df_zqy['工步状态'].str.contains('充电'))].copy()
#初始化部分的第一个循环充电后静置
initial_charge_stop_1=df_zqy[(df_zqy['循环序号']==1)&(df_zqy['工步状态'].str.contains('静置'))&(df_zqy['步次']==3)].copy()
#初始化部分的第一个循环放电部分
initial_discharge_1=df_zqy[(df_zqy['循环序号']==1)&(df_zqy['工步状态'].str.contains('放电'))].copy()
#初始化部分的第一个循环放电后静置
initial_discharge_stop_1=df_zqy[(df_zqy['循环序号']==1)&(df_zqy['工步状态'].str.contains('静置'))&(df_zqy['步次']==5)].copy()

def gen_data(cycle_num=3,step_tepe='充电'):
    # df2 = df[df['循环序号'] == 3].copy()  # 循环开始后的第一个循环号
    df2 = df[df['循环序号'] == cycle_num].copy()

    e_col=df2.filter(regex=f'{step_tepe}能量').columns[0] #能量列
    # charge_e = list(df2['充电能量(Wh)'])[0]  # 充电能量
    charge_e = list(df2[e_col])[0]  # 充电能量
    cap_col = df2.filter(regex=f'{step_tepe}容量').columns[0]  #容量列
    charge_cap = list(df2[cap_col])[0]  # 充电容量
    # charge_cap = list(df2['充电容量(Ah)'])[0]  # 充电容量

    # 第一步:确定该循环在记录层的数量及工步顺序(充电-搁置-放电-搁置)
    time_col=df2.filter(regex=f'{step_tepe}时间').columns[0]
    # print(f'time_col的值为{time_col}')
    charge_time_charge = list(df2['充电时间(h:min:s.ms)'])[0]
    charge_time = list(df2[time_col])[0]
    charge_seconds = re_type(charge_time)

    charge_num = math.ceil(charge_seconds / interval)+1  # 充电记录数量
    charge_power = charge_e / (charge_seconds / 3600)  # 充电功率

    df_charge=pd.DataFrame()    #充电记录
    for col in record.columns:
        df_charge[col]=[np.nan]*charge_num
    df_charge['记录号']=[i for i in range(1,df_charge.shape[0]+1)]
    df_charge['工步状态']='充电CP' if step_tepe=='充电' else '放电DP'
    df_charge['绝对时间'].iloc[0]=pd.to_datetime(list(df2['开始时间'])[0]) if step_tepe=='充电' else pd.to_datetime(list(df2['开始时间'])[0])+pd.to_timedelta(charge_time_charge)+datetime.timedelta(minutes=10)  #该循环的开始时间即为该循环充电工步的开始时间
    df_charge['相对时间(h:min:s.ms)'].iloc[0]=datetime.timedelta(hours=0,minutes=0,seconds=0,milliseconds=0)
    for row in range(1,df_charge.shape[0]):
        if row!=df_charge.shape[0]-1:
            df_charge['绝对时间'].iloc[row]=df_charge['绝对时间'].iloc[row-1]+datetime.timedelta(seconds=interval)
        else:
            # last_delta=charge_seconds-(charge_num-1)*30
            last_delta = charge_seconds - charge_num * interval
            df_charge['绝对时间'].iloc[row] = df_charge['绝对时间'].iloc[row-1] +datetime.timedelta(seconds=last_delta)+datetime.timedelta(seconds=20)
    # model_charge_record=record[(record['循环序号']==2)&(record['工步状态'].str.contains('充电'))].copy()
    model_charge_record=record[(record['循环序号']==2) & (record['工步状态'].str.contains(step_tepe))].copy()   #作用:以实测数据为基础,仿真产生一组新的电压数据
    model_charge_record['记录号']=[i for i in range(1,model_charge_record.shape[0]+1)]
    func=interp1d(x=model_charge_record['记录号'],y=model_charge_record['实际电压(V)'],kind='cubic')
    df_charge['记录号']=np.linspace(model_charge_record['记录号'].min(),model_charge_record['记录号'].max(),df_charge.shape[0])
    df_charge['实际电压(V)']=func(df_charge['记录号'])
    df_charge['实际电压(V)'].iloc[0]=df_charge['实际电压(V)'].iloc[0]+random.uniform(-0.0003,0.0003)    #起始电压
    df_charge['实际电压(V)'].iloc[-1]=df_charge['实际电压(V)'].iloc[-1]+random.uniform(-0.0001,0.0002)  # 结束电压

    df_charge['容量(Ah)'].iloc[0]=0
    df_charge['能量(Wh)'].iloc[0]=0
    df_charge['实际功率(W)']=[random.uniform(int(charge_power),int(charge_power)+1) for i in range(df_charge.shape[0])]
    df_charge['实际电流(A)']=df_charge['实际功率(W)']/df_charge['实际电压(V)']
    df_charge['温度2(℃)']=[random.uniform(45+random.uniform(0.9,1),df2['充电最大温度T2(℃)'].iloc[0]) for i in range(df_charge.shape[0])] if step_tepe=='充电' else [random.uniform(45+random.uniform(0.9,1),df2['放电最大温度T2(℃)'].iloc[0]) for i in range(df_charge.shape[0])]
    df_charge['温度3(℃)']=[random.uniform(45+random.uniform(0.9,1),df2['充电最大温度T3(℃)'].iloc[0]) for i in range(df_charge.shape[0])] if step_tepe=='充电' else [random.uniform(45+random.uniform(0.9,1),df2['放电最大温度T3(℃)'].iloc[0]) for i in range(df_charge.shape[0])]

    df_charge['记录号']=np.linspace(temp_material2_charge['记录号'].min(),temp_material2_charge['记录号'].max(),df_charge.shape[0]) if step_tepe=='充电' else np.linspace(temp_material2_discharge['记录号'].min(),temp_material2_discharge['记录号'].max(),df_charge.shape[0])
    df_charge['温度1(℃)']=func_charge_temp(df_charge['记录号']) if step_tepe=='充电' else func_discharge_temp(df_charge['记录号'])

    temp_delta=df2['充电初始温度T1(℃)'].iloc[0]-df_charge['温度1(℃)'].iloc[0] if step_tepe=='充电' else df2['放电初始温度T1(℃)'].iloc[0]-df_charge['温度1(℃)'].iloc[0]
    df_charge['温度1(℃)'].iloc[0]=df2['充电初始温度T1(℃)'].iloc[0] if step_tepe=='充电' else df2['放电初始温度T1(℃)'].iloc[0]
    df_charge['温度2(℃)'].iloc[0]=df2['充电初始温度T2(℃)'].iloc[0] if step_tepe=='充电' else df2['放电初始温度T2(℃)'].iloc[0]
    df_charge['温度3(℃)'].iloc[0]=df2['充电初始温度T3(℃)'].iloc[0] if step_tepe=='充电' else df2['放电初始温度T3(℃)'].iloc[0]
    df_charge['温度1(℃)'].iloc[1:]=df_charge['温度1(℃)'].iloc[1:]+temp_delta
    # 放电最大温度T1(℃)    放电最大温度T2(℃)    放电最大温度T3(℃)
    if step_tepe.__contains__('放电'):    #放电工步的结束温度用最大温度替代
        temp_delta2=df2['放电最大温度T1(℃)'].iloc[0]-df_charge['温度1(℃)'].iloc[-1]
        q_2_3=int(df_charge.shape[0]*0.7)
        temp_delta2_list=[random.uniform(0,temp_delta2) for i in range(len(df_charge['温度1(℃)'].iloc[q_2_3:]))] if temp_delta2>0 else [random.uniform(temp_delta2,0) for i in range(len(df_charge['温度1(℃)'].iloc[q_2_3:]))]
        temp_delta2_list.sort(key=abs)
        df_charge['温度1(℃)'].iloc[q_2_3:]=df_charge['温度1(℃)'].iloc[q_2_3:]+temp_delta2_list
        df_charge['温度1(℃)'].iloc[-1]=df2['放电最大温度T1(℃)'].iloc[0]
        df_charge['温度2(℃)'].iloc[-1]=df2['放电最大温度T2(℃)'].iloc[0]
        df_charge['温度3(℃)'].iloc[-1]=df2['放电最大温度T3(℃)'].iloc[0]
    if step_tepe.__contains__('充电'):
        temp_delta2=df2['充电最大温度T1(℃)'].iloc[0]-df_charge['温度1(℃)'].max()
        max_idx1=df_charge[df_charge['温度1(℃)']==df_charge['温度1(℃)'].max()].index[0]
        left_idx=int(0.3*df_charge.shape[0])
        temp_delta2_list = [random.uniform(0, temp_delta2) for i in range(len(df_charge['温度1(℃)'].iloc[left_idx:max_idx1]))] if temp_delta2 > 0 else [random.uniform(temp_delta2, 0) for i in range(len(df_charge['温度1(℃)'].iloc[left_idx:max_idx1]))]
        temp_delta2_list.sort(key=abs)
        df_charge['温度1(℃)'].iloc[left_idx:max_idx1] = df_charge['温度1(℃)'].iloc[left_idx:max_idx1] + temp_delta2_list
        temp_delta3_list = [random.uniform(0, temp_delta2) for i in range(len(df_charge['温度1(℃)'].iloc[max_idx1:]))] if temp_delta2 > 0 else [random.uniform(temp_delta2, 0) for i in range(len(df_charge['温度1(℃)'].iloc[max_idx1:]))]
        temp_delta3_list.sort(key=abs,reverse=True)
        df_charge['温度1(℃)'].iloc[max_idx1:] = df_charge['温度1(℃)'].iloc[max_idx1:] + temp_delta3_list

    for i in range(1,df_charge.shape[0]):
        avg_current=np.mean([df_charge['实际电流(A)'].iloc[i-1],df_charge['实际电流(A)'].iloc[i]])
        avg_power=np.mean([df_charge['实际功率(W)'].iloc[i-1],df_charge['实际功率(W)'].iloc[i]])
        if i!=df_charge.shape[0]-1:
            capacity_delta=avg_current*interval/3600  #该记录时间段产生的容量
            df_charge['容量(Ah)'].iloc[i]=df_charge['容量(Ah)'].iloc[i-1]+capacity_delta
            energy_delta=avg_power*interval/3600  #该记录时间段产生的能量
            df_charge['能量(Wh)'].iloc[i]=df_charge['能量(Wh)'].iloc[i-1]+energy_delta
        else:
            last_delta = charge_seconds - (charge_num - 2) * interval
            capacity_delta = avg_current * last_delta / 3600
            df_charge['容量(Ah)'].iloc[i] = df_charge['容量(Ah)'].iloc[i - 1] + capacity_delta
            energy_delta = avg_power * last_delta / 3600
            df_charge['能量(Wh)'].iloc[i] = df_charge['能量(Wh)'].iloc[i - 1] + energy_delta
    cap_k=charge_cap/list(df_charge['容量(Ah)'])[-1]
    df_charge['容量(Ah)']=df_charge['容量(Ah)']*cap_k
    energy_k = charge_e / list(df_charge['能量(Wh)'])[-1]
    df_charge['能量(Wh)'] = df_charge['能量(Wh)'] * energy_k
    df_charge['实际功率(W)']=df_charge['实际功率(W)']* energy_k
    df_charge['实际电流(A)'] = df_charge['实际电流(A)']*energy_k
    # df_charge['实际电流(A)'] = df_charge['实际电流(A)'] * random.uniform(energy_k,cap_k)
    df_charge['循环序号']=cycle_num
    if step_tepe=='充电':
        df_charge['单工步充电容量(Ah)']=df_charge['容量(Ah)']
        df_charge['单工步放电容量(Ah)']=0
        df_charge['单工步充电能量(Wh)']=df_charge['能量(Wh)']
        df_charge['单工步放电能量(Wh)']=0
    else:
        df_charge['单工步放电容量(Ah)']=df_charge['容量(Ah)']
        df_charge['单工步充电容量(Ah)']=0
        df_charge['单工步放电能量(Wh)']=df_charge['能量(Wh)']
        df_charge['单工步充电能量(Wh)']=0
    df_charge['累计充电容量(Ah)']=df_charge['单工步充电容量(Ah)']
    df_charge['累计放电容量(Ah)']=df_charge['单工步放电容量(Ah)']
    df_charge['累计充电能量(Wh)']=df_charge['单工步充电能量(Wh)']
    df_charge['累计放电能量(Wh)']=df_charge['单工步放电能量(Wh)']
    for j in range(1,df_charge.shape[0]):
        time_delta=pd.to_datetime(df_charge['绝对时间'].iloc[j])-pd.to_datetime(df_charge['绝对时间'].iloc[j-1])
        df_charge['相对时间(h:min:s.ms)'].iloc[j]=df_charge['相对时间(h:min:s.ms)'].iloc[j-1]+time_delta
    # df_charge['实际功率(W)']=df_charge['实际电流(A)']*df_charge['实际电压(V)']
    return df_charge

list_df=[]

for cycle in df[df['循环序号']<=2]['循环序号']:  #对循环层数据25度初始化的部分转化为记录层
    for step_type in ['充电','放电']:
        a = gen_data(cycle_num=cycle, step_tepe=step_type)
        for col in ['温度1(℃)','温度2(℃)','温度3(℃)','通道温差(℃)','电池最高温度(℃)','电池最低温度(℃)','电池平均温度(℃)']:
            a[col]=np.nan
        #初始温度
        a['温度1(℃)'].iloc[0]=list(df[df['循环序号']==cycle]['充电初始温度T1(℃)'])[0] if step_type=='充电' else list(df[df['循环序号']==cycle]['放电初始温度T1(℃)'])[0]
        a['温度2(℃)'].iloc[0]=list(df[df['循环序号']==cycle]['充电初始温度T2(℃)'])[0] if step_type=='充电' else list(df[df['循环序号']==cycle]['放电初始温度T2(℃)'])[0]
        a['温度3(℃)'].iloc[0]=list(df[df['循环序号']==cycle]['充电初始温度T3(℃)'])[0] if step_type=='充电' else list(df[df['循环序号']==cycle]['放电初始温度T3(℃)'])[0]
        #最大温度
        a['温度1(℃)'].iloc[1:]=[random.uniform(list(df[df['循环序号']==cycle]['充电初始温度T1(℃)'])[0],list(df[df['循环序号']==cycle]['充电最大温度T1(℃)'])[0]) for i in range(a.shape[0]-1)] if step_type=='充电' else [random.uniform(list(df[df['循环序号']==cycle]['放电初始温度T1(℃)'])[0],list(df[df['循环序号']==cycle]['放电最大温度T1(℃)'])[0]) for i in range(a.shape[0]-1)]
        a['温度2(℃)'].iloc[1:]=[random.uniform(list(df[df['循环序号']==cycle]['充电初始温度T2(℃)'])[0],list(df[df['循环序号']==cycle]['充电最大温度T2(℃)'])[0]) for i in range(a.shape[0]-1)] if step_type=='充电' else [random.uniform(list(df[df['循环序号']==cycle]['放电初始温度T2(℃)'])[0],list(df[df['循环序号']==cycle]['放电最大温度T2(℃)'])[0]) for i in range(a.shape[0]-1)]
        a['温度3(℃)'].iloc[1:]=[random.uniform(list(df[df['循环序号']==cycle]['充电初始温度T3(℃)'])[0],list(df[df['循环序号']==cycle]['充电最大温度T3(℃)'])[0]) for i in range(a.shape[0]-1)] if step_type=='充电' else [random.uniform(list(df[df['循环序号']==cycle]['放电初始温度T3(℃)'])[0],list(df[df['循环序号']==cycle]['放电最大温度T3(℃)'])[0]) for i in range(a.shape[0]-1)]

        list_df.append(a)
        last_time=list(a['绝对时间'])[-1]
        initial_charge_stop_1 = df_zqy[(df_zqy['循环序号'] == cycle) & (df_zqy['工步状态'].str.contains('静置'))].copy()
        if step_type=='充电': #该工步属于充电后的静置
            if len(initial_charge_stop_1['步次'].unique())>1:
                if initial_charge_stop_1[initial_charge_stop_1['步次']==initial_charge_stop_1['步次'].unique()[0]].shape[0]>100:
                    initial_charge_stop_1=initial_charge_stop_1[initial_charge_stop_1['步次']==initial_charge_stop_1['步次'].unique()[0]].copy()
                else:
                    initial_charge_stop_1 = initial_charge_stop_1[initial_charge_stop_1['步次']==initial_charge_stop_1['步次'].unique()[1]].copy()
            initial_charge_stop_1['记录号']=[i for i in range(initial_charge_stop_1.shape[0])]
            quiet_part=initial_charge_stop_1[initial_charge_stop_1['记录号']%interval==0].copy()
            quiet_part['绝对时间'].iloc[0]=last_time
            quiet_part['相对时间(h:min:s.ms)'].iloc[0]=datetime.timedelta(seconds=0)
            quiet_part['容量(Ah)']=0
            quiet_part['能量(Wh)']=0
            quiet_part['实际电流(A)']=0
            quiet_part['实际功率(W)']=0
            quiet_part['工步状态']='静置'
            quiet_part['单工步充电容量(Ah)']=0
            quiet_part['单工步放电容量(Ah)']=0
            quiet_part['单工步充电能量(Wh)']=0
            quiet_part['单工步放电能量(Wh)']=0
            quiet_part['累计充电容量(Ah)']=0
            quiet_part['累计放电容量(Ah)']=0
            quiet_part['累计充电能量(Wh)']=0
            quiet_part['累计放电能量(Wh)']=0
            quiet_part['循环序号'] = cycle
            for i in range(1, quiet_part.shape[0]):
                quiet_part['绝对时间'].iloc[i] = quiet_part['绝对时间'].iloc[i - 1] + datetime.timedelta(seconds=interval)
            list_df.append(quiet_part)
        else:    #放电后静置的部分
            if cycle==1:
                if len(initial_charge_stop_1['步次'].unique()) >= 1:
                    initial_charge_stop_1 = initial_charge_stop_1[initial_charge_stop_1['步次'] ==initial_charge_stop_1['步次'].unique()[-1]].copy()
                initial_charge_stop_1['记录号'] = [i for i in range(initial_charge_stop_1.shape[0])]
                quiet_part = initial_charge_stop_1[initial_charge_stop_1['记录号'] % interval == 0].copy()
                quiet_part['绝对时间'].iloc[0] = last_time
                quiet_part['相对时间(h:min:s.ms)'].iloc[0] = datetime.timedelta(seconds=0)
                quiet_part['容量(Ah)'] = 0
                quiet_part['能量(Wh)'] = 0
                quiet_part['实际电流(A)'] = 0
                quiet_part['实际功率(W)'] = 0
                quiet_part['工步状态'] = '静置'
                quiet_part['单工步充电容量(Ah)'] = 0
                quiet_part['单工步放电容量(Ah)'] = 0
                quiet_part['单工步充电能量(Wh)'] = 0
                quiet_part['单工步放电能量(Wh)'] = 0
                quiet_part['累计充电容量(Ah)'] = 0
                quiet_part['累计放电容量(Ah)'] = 0
                quiet_part['累计充电能量(Wh)'] = 0
                quiet_part['累计放电能量(Wh)'] = 0
                quiet_part['循环序号'] = cycle
                for i in range(1, quiet_part.shape[0]):
                    quiet_part['绝对时间'].iloc[i] = quiet_part['绝对时间'].iloc[i - 1] + datetime.timedelta(seconds=interval)
                    quiet_part['相对时间(h:min:s.ms)'].iloc[i] = quiet_part['相对时间(h:min:s.ms)'].iloc[i - 1] + datetime.timedelta(seconds=interval)
                list_df.append(quiet_part)
            # quiet_part = pd.DataFrame()
            else:   #该静置工步时长为5小时
                five_hours_stop['记录号']=[i for i in range(five_hours_stop.shape[0])]

                func_v_stop=interp1d(x=five_hours_stop['记录号'],y=five_hours_stop['实际电压(V)'],kind='cubic')

                quiet_part=pd.DataFrame()
                for col2 in initial_charge_stop_1.columns:
                    quiet_part[col2]=[np.nan for i in range(1801)]
                quiet_part['记录号']=np.linspace(five_hours_stop['记录号'].min(),five_hours_stop['记录号'].max(),quiet_part.shape[0])
                quiet_part['实际电压(V)']=func_v_stop(quiet_part['记录号'])
                quiet_part['绝对时间'].iloc[0] = last_time
                quiet_part['相对时间(h:min:s.ms)'].iloc[0] = datetime.timedelta(seconds=0)
                quiet_part['容量(Ah)'] = 0
                quiet_part['能量(Wh)'] = 0
                quiet_part['实际电流(A)'] = 0
                quiet_part['实际功率(W)'] = 0
                quiet_part['工步状态'] = '静置'
                quiet_part['单工步充电容量(Ah)'] = 0
                quiet_part['单工步放电容量(Ah)'] = 0
                quiet_part['单工步充电能量(Wh)'] = 0
                quiet_part['单工步放电能量(Wh)'] = 0
                quiet_part['累计充电容量(Ah)'] = 0
                quiet_part['累计放电容量(Ah)'] = 0
                quiet_part['累计充电能量(Wh)'] = 0
                quiet_part['累计放电能量(Wh)'] = 0
                quiet_part['温度1(℃)']=0
                quiet_part['温度2(℃)'] = 0
                quiet_part['温度3(℃)'] = 0
                quiet_part['通道温差(℃)'] = 0
                quiet_part['电池最高温度(℃)'] = 0
                quiet_part['电池最低温度(℃)'] = 0
                quiet_part['电池平均温度(℃)'] = 0
                quiet_part['循环序号'] = cycle
                for i in range(1, quiet_part.shape[0]):
                    quiet_part['绝对时间'].iloc[i] = quiet_part['绝对时间'].iloc[i - 1] + datetime.timedelta(seconds=interval)
                    quiet_part['相对时间(h:min:s.ms)'].iloc[i] = quiet_part['相对时间(h:min:s.ms)'].iloc[i - 1] + datetime.timedelta(seconds=interval)
                list_df.append(quiet_part)

for cycle in df[df['循环序号']>2]['循环序号']:  #对循环层数据25度初始化后面的部分(也就是循环开始的部分)转化为记录层
    for step_type in ['充电','放电']:
        a=gen_data(cycle_num=cycle,step_tepe=step_type)
        print(f'{datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")}---第{cycle}圈{step_type}a的值为{a}')
        list_df.append(a)
        last_time=list(a['绝对时间'])[-1]   #充电(放电)工步的最后一个记录的绝对时间,即为下一静置工步的开始时间
        quiet_part=pd.DataFrame()   #静置的记录
        for col in a.columns:
            quiet_part[col]=[np.nan for i in range(int(600/interval)+1)]     #一个静置工步时长固定为10min,假设采样间隔是10s/个
        quiet_part['绝对时间'].iloc[0]=last_time
        if step_type == '充电':   #充电后静置工步处理
            quiet_part['记录号']=np.linspace(stop_after_charge['记录号'].min(),stop_after_charge['记录号'].max(),quiet_part.shape[0])
            quiet_part['实际电压(V)']=stop_func_c(quiet_part['记录号'])
            initial_charge_stop_1=df_zqy[(df_zqy['循环序号'] == 1) & (df_zqy['工步状态'].str.contains('静置')) & (df_zqy['步次'] == 3)].copy()
            initial_charge_stop_1['记录号']=[i for i in range(initial_charge_stop_1.shape[0])]
            initial_charge_stop_2=initial_charge_stop_1[initial_charge_stop_1['记录号'] % interval == 0].copy()
            quiet_part['温度1(℃)']=list(initial_charge_stop_2['温度1(℃)'])
            quiet_part['温度1(℃)']=quiet_part['温度1(℃)'].apply(lambda x:x+20)
            quiet_part['温度2(℃)']=list(initial_charge_stop_2['温度2(℃)'])
            quiet_part['温度2(℃)']=quiet_part['温度2(℃)'].apply(lambda x:x+20)
            quiet_part['温度3(℃)']=list(initial_charge_stop_2['温度3(℃)'])
            quiet_part['温度3(℃)']=quiet_part['温度3(℃)'].apply(lambda x:x+20)
        elif step_type=='放电':   #放电后静置工步处理
            quiet_part['记录号']=np.linspace(stop_after_discharge['记录号'].min(),stop_after_discharge['记录号'].max(),quiet_part.shape[0])
            quiet_part['实际电压(V)'] = stop_func_d(quiet_part['记录号'])
            initial_discharge_stop_1 = df_zqy[(df_zqy['循环序号'] == 1) & (df_zqy['工步状态'].str.contains('静置')) & (df_zqy['步次'] == 5)].copy()
            initial_discharge_stop_1['记录号'] = [i for i in range(initial_discharge_stop_1.shape[0])]
            initial_discharge_stop_2 = initial_discharge_stop_1[initial_discharge_stop_1['记录号'] % interval == 0].copy()
            quiet_part['温度1(℃)'] = list(initial_discharge_stop_2['温度1(℃)'])
            quiet_part['温度1(℃)']=quiet_part['温度1(℃)'].apply(lambda x:x+20)
            quiet_part['温度2(℃)'] = list(initial_discharge_stop_2['温度2(℃)'])
            quiet_part['温度2(℃)'] = quiet_part['温度2(℃)'].apply(lambda x: x + 20)
            quiet_part['温度3(℃)'] = list(initial_discharge_stop_2['温度3(℃)'])
            quiet_part['温度3(℃)'] = quiet_part['温度3(℃)'].apply(lambda x: x + 20) #充电后静置工步处理

        quiet_part['相对时间(h:min:s.ms)'].iloc[0]=datetime.timedelta(seconds=0)
        quiet_part['容量(Ah)']=0
        quiet_part['能量(Wh)'] = 0
        quiet_part['实际电流(A)'] = 0
        quiet_part['实际功率(W)'] = 0
        quiet_part['工步状态'] = '静置'
        quiet_part['单工步充电容量(Ah)'] = 0
        quiet_part['单工步放电容量(Ah)'] = 0
        quiet_part['单工步充电能量(Wh)'] = 0
        quiet_part['单工步放电能量(Wh)'] = 0
        quiet_part['累计充电容量(Ah)'] = 0
        quiet_part['累计放电容量(Ah)'] = 0
        quiet_part['累计充电能量(Wh)'] = 0
        quiet_part['累计放电能量(Wh)'] = 0
        quiet_part['循环序号']=cycle
        for i in range(1,quiet_part.shape[0]):
            quiet_part['绝对时间'].iloc[i]=quiet_part['绝对时间'].iloc[i-1]+datetime.timedelta(seconds=interval)
            quiet_part['相对时间(h:min:s.ms)'].iloc[i] = quiet_part['相对时间(h:min:s.ms)'].iloc[i - 1] + datetime.timedelta(seconds=interval)
        list_df.append(quiet_part)

df_over=pd.concat(objs=list_df)

df_over['记录号']=[i for i in range(1,df_over.shape[0]+1)]
df_over['中位机记录号']=df_over['记录号']
df_over['相对时间(h:min:s.ms)']=pd.to_timedelta(df_over['相对时间(h:min:s.ms)'])
df_over['设定功率(W)']=502
for i in range(df_over.shape[0]):
    step_state=df_over['工步状态'].iloc[i]
    if step_state.__contains__('充电') or step_state.__contains__('放电'):
        pass
    else:
        df_over['设定功率(W)'].iloc[i]=0

df_over['工步序号'].iloc[0]=1
df_over['连续时间'].iloc[0]=0
df_over['连续时间(h:min:s.ms)'].iloc[0]=datetime.timedelta(hours=0,minutes=0,seconds=0,milliseconds=0)
# delta.total_seconds()
for i in range(1,df_over.shape[0]):
    if df_over['工步状态'].iloc[i]==df_over['工步状态'].iloc[i-1]:
        time_delta = df_over['相对时间(h:min:s.ms)'].iloc[i]-df_over['相对时间(h:min:s.ms)'].iloc[i-1]
        df_over['连续时间(h:min:s.ms)'].iloc[i]=df_over['连续时间(h:min:s.ms)'].iloc[i-1]+time_delta
    else:
        df_over['连续时间(h:min:s.ms)'].iloc[i]=df_over['连续时间(h:min:s.ms)'].iloc[i-1]

for i in range(1,df_over.shape[0]):
    if df_over['工步状态'].iloc[i]!=df_over['工步状态'].iloc[i-1]:  #工步状态较上一个记录发生了改变
        df_over['工步序号'].iloc[i]=df_over['工步序号'].iloc[i-1]+1
    else:
        df_over['工步序号'].iloc[i] = df_over['工步序号'].iloc[i-1]
df_over['步次']=df_over['工步序号']

df_over['连续时间(h:min:s.ms)']=pd.to_timedelta(df_over['连续时间(h:min:s.ms)'])
df_over['连续时间']=df_over['连续时间(h:min:s.ms)'].dt.days*24*3600*1000+df_over['连续时间(h:min:s.ms)'].dt.microseconds/1000+df_over['连续时间(h:min:s.ms)'].dt.seconds*1000
df_over['实际电压(V)']=df_over['实际电压(V)'].round(4)
df_over['实际电流(A)']=df_over['实际电流(A)'].round(4)
df_over['实际功率(W)']=df_over['实际电压(V)']*df_over['实际电流(A)']
df_over['实际功率(W)']=df_over['实际功率(W)'].round(4)
df_over['容量(Ah)']=df_over['容量(Ah)'].round(4)
df_over['能量(Wh)']=df_over['能量(Wh)'].round(4)

df_over['单工步充电容量(Ah)']=df_over['单工步充电容量(Ah)'].round(4)
df_over['单工步放电容量(Ah)']=df_over['单工步放电容量(Ah)'].round(4)
df_over['单工步充电能量(Wh)']=df_over['单工步充电能量(Wh)'].round(4)
df_over['单工步放电能量(Wh)']=df_over['单工步放电能量(Wh)'].round(4)
df_over['累计充电容量(Ah)']=df_over['累计充电容量(Ah)'].round(4)
df_over['累计放电容量(Ah)']=df_over['累计放电容量(Ah)'].round(4)
df_over['累计充电能量(Wh)']=df_over['累计充电能量(Wh)'].round(4)
df_over['累计放电能量(Wh)']=df_over['累计放电能量(Wh)'].round(4)

list1=[]
for i in range(df_over.shape[0]):
    seconds=df_over['连续时间(h:min:s.ms)'].iloc[i].total_seconds()
    hours = seconds // 3600
    minutes = (seconds - hours * 3600) // 60
    second = seconds - hours * 3600 - minutes * 60
    millsecond = (seconds-int(seconds))*1000
    list1.append(f'{format(int(hours),"02")}:{format(int(minutes),"02")}:{format(int(second),"02")}.{format(round(int(millsecond),-1),"03")}')
df_over['连续时间(h:min:s.ms)']=list1
df_over['连续时间(h:min:s.ms)']=df_over['连续时间(h:min:s.ms)'].apply(lambda x:' '+str(x))

list2=[]
for i in range(df_over.shape[0]):
    seconds=df_over['相对时间(h:min:s.ms)'].iloc[i].total_seconds()
    hours = seconds // 3600
    minutes = (seconds - hours * 3600) // 60
    second = seconds - hours * 3600 - minutes * 60
    millsecond = (seconds-int(seconds))*1000
    list2.append(f'{format(int(hours),"02")}:{format(int(minutes),"02")}:{format(int(second),"02")}.{format(round(int(millsecond),-1),"03")}')
df_over['相对时间(h:min:s.ms)']=list2
df_over['相对时间(h:min:s.ms)']=df_over['相对时间(h:min:s.ms)'].apply(lambda x:' '+str(x))

df_over['绝对时间']=pd.to_datetime(df_over['绝对时间'])
list3=[]
for i in range(df_over.shape[0]):
    year=df_over['绝对时间'].iloc[i].year
    month=df_over['绝对时间'].iloc[i].month
    day=df_over['绝对时间'].iloc[i].day
    hour=df_over['绝对时间'].iloc[i].hour
    minute=df_over['绝对时间'].iloc[i].minute
    second=df_over['绝对时间'].iloc[i].second
    microsecond=df_over['绝对时间'].iloc[i].microsecond
    list3.append(f'{year}-{format(month,"02")}-{format(day,"02")} {format(hour,"02")}:{format(minute,"02")}:{format(second,"02")}.{format(int(microsecond/1000),"03")}')
# # 2024-07-21 00:07:57.800
df_over['绝对时间']=list3
df_over['绝对时间']=df_over['绝对时间'].apply(lambda x:' '+str(x))

df_over['温度1(℃)']=df_over['温度1(℃)'].round(1)
df_over['温度2(℃)']=df_over['温度2(℃)'].round(1)
df_over['温度3(℃)']=df_over['温度3(℃)'].round(1)
for i in range(df_over.shape[0]):
    df_over['电池最高温度(℃)'].iloc[i] = np.max([df_over['温度1(℃)'].iloc[i], df_over['温度2(℃)'].iloc[i], df_over['温度3(℃)'].iloc[i]])
    df_over['电池最低温度(℃)'].iloc[i] = np.min([df_over['温度1(℃)'].iloc[i], df_over['温度2(℃)'].iloc[i], df_over['温度3(℃)'].iloc[i]])
    df_over['电池平均温度(℃)'].iloc[i] = np.mean([df_over['温度1(℃)'].iloc[i], df_over['温度2(℃)'].iloc[i], df_over['温度3(℃)'].iloc[i]])
    df_over['通道温差(℃)'].iloc[i] = df_over['电池最高温度(℃)'].iloc[i] - df_over['电池最低温度(℃)'].iloc[i]
df_over['电池平均温度(℃)']=df_over['电池平均温度(℃)'].round(4)
df_over['通道温差(℃)']=df_over['通道温差(℃)'].round(1)

first_record=openpyxl.Workbook()
print(type(first_record))

goal_excel=r'C:\Users\数据处理\Desktop\完成\第二组记录层.xlsx'
if not os.path.exists(goal_excel):
    first_record.save(goal_excel)   #创建一个新的excel文件
    print(f'{datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")}第二组记录层excel创建成功')

app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False

wb=app.books.open(goal_excel)

if df_over.shape[0]>1000000:  #假设每100万条数据放在一个sheet页
    for i in range(1000000,df_over.shape[0]+1000000,1000000):
        df_over2=df_over.iloc[i-1000000:i,:].copy()
        record_part=int(i/1000000)
        if f'记录层{record_part}' not in wb.sheet_names:
            wb.sheets.add(f'记录层{record_part}',after=wb.sheet_names[-1])
        ws=wb.sheets[f'记录层{record_part}']
        ws.range((1,1),(1,df_over2.shape[1])).options(ndim=1).value = df_over2.columns.values.tolist()
        ws.range((2,1),(1+df_over2.shape[0],df_over2.shape[1])).options(ndim=2).value=pd.DataFrame(df_over2).astype(str).values.tolist()
        print(f'{datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")} 记录层{record_part}数据写入成功')
else:   #否则该记录层的数据单独放入一个sheet页
    if '记录层' not in wb.sheet_names:
        wb.sheets.add('记录层')
    ws=wb.sheets['记录层']
    ws.range((1,1),(1,df_over.shape[1])).options(ndim=1).value=df_over.columns.values.tolist()
    ws.range((2,1),(1+df_over.shape[0],df_over.shape[1])).options(ndim=2).value=pd.DataFrame(df_over).astype(str).values.tolist()
    print(f'{datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")} 记录层数据写入成功')

# 删除的sheet页
delete_sheet_list=[sheet for sheet in wb.sheet_names if sheet.__contains__('Sheet')]
for i in delete_sheet_list:
    wb.sheets[i].delete()
    print(f'{datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")} 工作簿{i}删除成功')

wb.save(goal_excel)
print(f'{datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")} 记录层保存完成')
wb.close()
app.quit()
app.kill()

